package sliver.dao.impl;

import java.io.Serializable;
import java.math.BigInteger;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.jdbc.Work;
import org.springframework.stereotype.Repository;

import sliver.dao.AdvertisementDao;
import sliver.entity.Advertisement;
import sliver.entity.Custom;

@Repository
public class AdvertisementDaoImpl extends BaseDaoImpl<Advertisement> implements AdvertisementDao{

	@Override
	public List<Advertisement> findList(Serializable advertiserId,
			Integer position) {
		
		String queryString = "SELECT ad " +
		  		 "FROM Advertisement ad, Advertiser ader " +
		  		 "WHERE ad.advertiser.id = ader.id " +
		  		 "AND ader.id = ? ";
		
		if(position != null){
			queryString += "AND ad.position = ?";
		}
		
		return complexQuery(queryString, new Object[]{advertiserId, position});
	}
	
	@Override
	public List<Advertisement> matching(Serializable shopperId, int position, int ranking) {
		
		String queryString = "SELECT ad.id," +
							 "       ROUND((COUNT(*)/(SELECT COUNT(*) FROM box_rel_shopper_tag WHERE shopper_id = 1)) * 100 ,2) + ROUND((ad.bid / ad.expect_show_num) * 100, 2) as scores " +
							 "FROM box_advertisement ad, " +
							 "     box_tag tag, " +
							 "     box_rel_advertisement_tag rel_ad_tag, " +
							 "     box_rel_advertisement_province rel_ad_p, " +
							 "     box_shopper shop " + 
							 "WHERE " +
							 "	   ad.id = rel_ad_tag.advertisement_id " +
							 "AND " +  
							 "	   rel_ad_tag.tag_id = tag.id " +
							 "AND " +
							 "     rel_ad_p.advertisement_id = ad.id " +
							 "AND " +
							 "     rel_ad_p.province_id = shop.province_id " +
							 "AND " +
							 "	   ad.position = ? " +
							 "AND " +
							 "	   ad.state = 0 " +
							 "AND " +
							 "     shop.id = ? " +
							 "AND " +
							 "     locate(hour(now()), ad.show_times) > 0 " +
							 "AND " + 
							 "     tag.id in (SELECT " +
							 "					  rel_shop_tag.tag_id AS tag_id " +
							 "                FROM " +  
							 "					  box_rel_shopper_tag rel_shop_tag " +
							 "				  WHERE " + 
							 "					  rel_shop_tag.shopper_id = ?) " +
							 "GROUP BY ad.id " +
							 "ORDER BY scores DESC " +
							 "LIMIT 0, ?";
		
		List<Object> rows = complexQueryFields(queryString, new Object[]{position, shopperId, shopperId, ranking});
		
		List<Advertisement> advertisements = new ArrayList<Advertisement>();
		for(Object row : rows){
			Object[] columns = (Object[])row;
			BigInteger id = (BigInteger)columns[0];
			
			advertisements.add(findById(id.longValue(), Advertisement.class));
		}
		
		return advertisements;
	}
	
	@Override
	public Map<Advertisement, Double> matching(final Serializable shopperId,final Custom custom,final int position,
			final int ranking) {
		
		final Map<Advertisement, Double> advertisements = new LinkedHashMap<Advertisement, Double>();
		
		getCurrentSession().doWork(new Work() {
			
			@Override
			public void execute(Connection connection) throws SQLException {
				CallableStatement callableStatement = null;
				
				callableStatement = connection.prepareCall("{call keywordsMatchingPoints(?,?,?,?)}");
				callableStatement.setInt(1, (Integer)shopperId);
				callableStatement.setLong(2, custom.getId());
				callableStatement.setInt(3, position);
				callableStatement.setInt(4, ranking);
				
				boolean isHandle = callableStatement.execute();
				while(isHandle){
					
					ResultSet sets = callableStatement.getResultSet();
					while(sets != null && sets.next()){
						
						Long id = sets.getLong(1);
						Double keysCost = sets.getDouble(2);
						
						advertisements.put(findById(id, Advertisement.class), keysCost);
					}
					
					isHandle = callableStatement.getMoreResults();
				}
			}
		});
		
		return advertisements;
	}
}
